package iedcyt.acute.dataAccess.dao.acute1;

import iedcyt.acute.dataAccess.domain.acute1.Author_Data;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.generic.StringRowMapper;
import iedcyt.acute.dataAccess.idao.acute1.IAuthorDataDao;

import java.util.List;



public class AuthorDataDao extends SpringDao implements IAuthorDataDao  {

	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from Author_Data");
		} catch (Exception e) {
			_jdbcTemplate.execute("CREATE TABLE Author_Data (idAu numeric, AuthorData varchar(50), AuthorValue varchar(50))");
			_jdbcTemplate.execute("CREATE INDEX idxAuthor_Data1 on Author_Data (idAu, AuthorData) WITH PRIMARY");
			_jdbcTemplate.execute("CREATE INDEX idxAuthor_Data2 on Author_Data (idAu)");
			_jdbcTemplate.execute("ALTER TABLE Author_Data ADD FOREIGN KEY (idAu) REFERENCES Author(idAu)");
		}
	}
	
	@Override
	public List<Author_Data> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM Author_Data",
				new AuthorDataRowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from Author_Data");
	}

	

	@Override
	public void insert(Author_Data auD) {
	
	 _jdbcTemplate.update("INSERT INTO Author_Data (idAu, AuthorData, AuthorValue) VALUES (?,?,?)",
				new Object[] { auD.get_idAu(), auD.get_authorData(), auD.get_authorValue() });
		
		
	}
	
	@Override
	public void update(Author_Data auD) {
	
		 _jdbcTemplate.update("UPDATE Author_Data SET  AuthorValue = ? WHERE idAu = ? AND AuthorData = ?",
				new Object[] { auD.get_authorValue(), auD.get_idAu(), auD.get_authorData()  });
		
	}

	
	@Override
	public void updateOrInsert(Author_Data auD) {

		//Compruebo si ya existe
		if (_jdbcTemplate.query("SELECT * FROM Author_Data WHERE idAu = ? AND AuthorData = ?",
				new Object[] { auD.get_idAu(), auD.get_authorData() },
				new AuthorDataRowMapper()).size() == 0)
		{
			_jdbcTemplate.update("INSERT INTO Author_Data (idAu, AuthorData, AuthorValue) VALUES (?,?,?)",
					new Object[] { auD.get_idAu(), auD.get_authorData(), auD.get_authorValue() });
			
		}
		else 
		{
			_jdbcTemplate.update("UPDATE Author_Data SET  AuthorValue = ? WHERE idAu = ? AND AuthorData = ?",
					new Object[] { auD.get_authorValue(), auD.get_idAu(), auD.get_authorData()  });
			
		}
	}
		
	@Override
	public void deleteByIdAu(int idAu) {
		
		_jdbcTemplate.update("DELETE from Author_Data WHERE idAu=?",
				new Object[] { idAu  });
		
	}

	@Override
	public void deleteByIdAuByAuthorData(int idAu, String dataName) {
		
		_jdbcTemplate.update("DELETE from Author_Data WHERE idAu=? AND AuthorData =?",
				new Object[] { idAu, dataName  });
		
	}

	
	@Override
	public List<Author_Data> selectByAuthorData(String dataName) {
		return _jdbcTemplate.query("SELECT * FROM Author_Data WHERE AuthorData = ?",
				new Object[] { dataName },
				new AuthorDataRowMapper());
	}

	@Override
	public List<Author_Data> selectByIdAuAndAuthorData(int idAu, String dataName) {
		return _jdbcTemplate.query("SELECT * FROM Author_Data WHERE idAu = ? AND AuthorData = ?",
				new Object[] { idAu, dataName },
				new AuthorDataRowMapper());
	}

	@Override
	public List<Author_Data> select(int idAu) {
		return _jdbcTemplate.query("SELECT * FROM Author_Data WHERE idAu = ?",
				new Object[] { idAu },
				new AuthorDataRowMapper());
	}

	/**
	 * @see iedcyt.acute.dataAccess.idao.acute1.IAuthorDataDao#selectDistinctsDataNames()
	 */
	@Override
	public List<String> selectDistinctsDataNames() {
		return _jdbcTemplate
		.query(
				"SELECT AuthorData FROM Author_Data GROUP BY AuthorData",
				new StringRowMapper());
	}

	

}

